const express = require('express');
const mysql = require('mysql');
const bodyParser = require('body-parser');
const pool = mysql.createPool({
  host: '127.0.0.1',   //MySQL服务器地址
  port: 3306,          //MySQL服务器端口号
  user: 'root',        //数据库用户的用户名
  password: '',        //数据库用户密码
  database: 'wts',    //数据库名称
  connectionLimit: 20, //最大连接数
  charset: 'utf8'      //数据库服务器的编码方式
});
const server = express();
//托管静态资源目录
server.use(express.static('./public'))
//添加中间键
server.use(bodyParser.urlencoded({
  extended: false
}));
const cors = require('cors');
server.use(cors({
  origin: '*'
}));
//首页轮播图  测试完成 
server.get('/swipe',(req,res)=>{
let sql='select cid,img from wts_carousel'
 pool.query(sql,(error,result)=>{
 if(error) throw error   
 res.send({message:'ok',code:200,result:result})
})
})
//注册  测试完成
server.post('/register',(req,res)=>{
  let user_name=req.body.user_name
  let name=req.body.uname
  let upwd=req.body.upwd
  let phone=req.body.phone
  // console.log(user_name,name,upwd,phone)
  let sql = 'SELECT COUNT(uid) AS count FROM wts_user WHERE uname=?';
  pool.query(sql, [name], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT wts_user(uname,upwd,user_name,phone) VALUES(?,?,?,?)';
      pool.query(sql, [name,upwd,user_name,phone], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'uname exists', code: 201 });
    }
  });
})
//登录接口 
// 登录 测试完成
server.post('/login',(req,res)=>{
  console.log(req.body)
  let phone=req.body.phone
  let pwd=req.body.upwd
  let sql='select uid,uname,upwd,phone,user_name  from wts_user where phone=? and upwd=?'
  pool.query(sql,[phone,pwd],(error,result)=>{
    if(error) throw error
    console.log(result);
    if(result.length==0){
      res.send({code:201,msg:'login failed'})
    }else{
      res.send({msg:'ok',code:200,result:result[0]});
    }
  })
})

//详情页接口
server.get('/detail',(req,res)=>{
  console.log(req.body)
  let id=req.query.id
  let sql=`select * from wts_laptop where lid=${id} `
  pool.query(sql,id,(error,result)=>{
    if(error) throw error
    console.log(result);
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result[0]});
    }
  })
})

//门票表查询
server.get('/menpiao',(req,res)=>{
  let lid = req.query.lid
  let sql =` select * from wts_menpiao where family_id=${lid}`
  pool.query(sql,(error,results)=>{
    if(error) throw error
   if(results.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:results});
    }
  })
})

//热门推荐查询

server.get('/hotmudi',(req,res)=>{
  let sql='select * from wts_menpiao ORDER BY spec DESC LIMIT 0,4'
   pool.query(sql,(error,results)=>{
   if(error) throw error   
   if(results.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:results});
    }
  })
})
//搜索查询
 
server.post('/sousuo',(req,res)=>{
  let dd = req.body.dd
  let sql =` select * from wts_laptop where title like + '%${dd}%' `
  pool.query(sql,(error,results)=>{
    if(error) throw error
   if(results.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:results});
    }
  })
})
//收藏添加接口
server.post('/shoucang',(req,res)=>{
  let uname=req.body.uname
  let lid=req.body.lid
  let price=req.body.price
  let title=req.body.title
  let spec=req.body.spec
  let pic=req.body.pic
  
  let  sql='SELECT COUNT(id) AS count FROM wts_myshoucang where lid=? and uname=?'
  pool.query(sql,[lid,uname],(error,results)=>{
	  
	  if(error) throw error;
	  let count=results[0].count
	  console.log(count)
	  if(count==0){
	let  sql = 'INSERT wts_myshoucang(uname,lid,price,title,spec,pic) VALUES(?,?,?,?,?,?)';	  
		pool.query(sql, [uname,lid,price,title,spec,pic], (error) => {
    if(error){throw error}
    else{
       res.send({msg:'ok',code:200});
   } 
   });  	    
	  }else{
		  
		  res.send({msg:'lid exists',code:201});
	  }  
  })
})  
//收藏删除接口
server.delete('/del_sc', (req, res, next) => {
  let lid = req.query.lid
  let uname=req.query.uname
  let sql = 'delete from wts_myshoucang where lid=? and uname=?'
  pool.query(sql, [lid,uname], (error, results) => {
    if (error) {
      next(error)
      return
    }
    if (results.affectedRows == 0) {
      res.send({ message: 'fail to delete ', code: 201 })
    } else {
      res.send({ message: 'successfully delete', code: 200 })
    }
  })
})
//收藏查询接口
server.get('/sc_cha',(req,res)=>{
  let uname=req.query.uname

  let sql=`select * from wts_myshoucang where uname='${uname}'`
 
  pool.query(sql,uname,(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
//订单状态查询接口
server.get('/dd_cha',(req,res)=>{
  let uname=req.query.uname
  let ordeail_id=req.query.ordeail_id
  let sql= `select * from wts_ordetail inner join wts_laptop on wts_ordetail.lid=wts_laptop.lid where uname='${uname}' and ordeail_id='${ordeail_id}'`
 
  pool.query(sql,[uname,ordeail_id],(error,result)=>{
    if(error) throw error
  
    if(result.length==0){
      res.send({code:404,msg:'not found'})
    }else{
      res.send({msg:'ok',code:200,result:result});
    }
  })
})
 
//订单添加接口
server.post('/ddAdd',(req,res)=>{
  let uname=req.body.uname
  let name=req.body.uname
  let yhsf=req.body.yhsf
  let phone=req.body.phone
  let ordeail_id=req.body.ordeail_id
  let price=req.body.price
  let datetime=req.body.datetime
  
  
  // console.log(user_name,name,upwd,phone)
  let sql = 'SELECT COUNT(uid) AS count FROM wts_user WHERE uname=?';
  pool.query(sql, [name], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = 'INSERT wts_user(uname,upwd,user_name,phone) VALUES(?,?,?,?)';
      pool.query(sql, [name,upwd,user_name,phone], (error, results) => {
        if (error) throw error;
        res.send({ message: 'ok', code: 200 });
      })
    } else {
      res.send({ message: 'uname exists', code: 201 });
    }
  });
})

// ******************************************************************************


//用户信息查询
server.get('/adminUser',(req,res)=>{
  let sql='select * from wts_user'
  pool.query(sql,(error,result)=>{
    if(error) throw error   
    res.send({message:'ok',code:200,result:result})
  })
})

//测试后台登录页面
server.post('/adminLogin',(req,res)=>{
  let admin_name=req.body.admin_name
  let admin_pwd=req.body.admin_pwd
  let sql='select admin_name,admin_pwd  from wts_admin where admin_name=? and admin_pwd=?'
  pool.query(sql,[admin_name,admin_pwd],(err,result)=>{
    if(err) throw err
    console.log(result);
    if(res.length==0){
      res.send({code:201,msg:'login failed'})
    }else{
      res.send({msg:'ok',code:200});
    }
  })
}),

//搜索查询
server.post('/adminSousuo',(req,res)=>{
   let dd = req.body.dd
   let sql =` select * from wts_user where phone like + '%${dd}%' `
   pool.query(sql,(error,results)=>{
    if(error) throw error
    if(results.length==0){
        res.send({code:404,msg:'not found'})
      }else{
        res.send({msg:'ok',code:200,result:results});
      }
   })
  })



  //分页查询
server.get('/adminUserPno', (req, res) => {
  //获取get传递的参数
  //如果没有传参，设置默认值
  //如果页码为空，设置默认值为1
  if (!req.query.page) {
    req.query.page = 1
  }
  //计算开始查询的值
  let count=Number(req.query.count )
  let start = (req.query.page - 1) * count
  //执行SQL命令，查询数据
  pool.query(
    'select * from wts_user limit ?,?',
    [start,count],
    (error, results) => {
      if (error) throw error
      // console.log(results)
      res.send({ code: 200, msg: 'ok', data: results })
    }
  )
})

//查询用户数量
server.get('/countUser',(req,res)=>{
  let sql='select count(*)  from wts_user'
  pool.query(sql,(error,result)=>{
    if(error)throw error
    res.send({message:'ok',code:200,result:result})
  })
})
//查询景点数量
server.get('/countLaptop',(req,res)=>{
  let sql='select count(*)  from wts_laptop'
  pool.query(sql,(error,result)=>{
    if(error)throw error
    res.send({message:'ok',code:200,result:result})
  })
})
//查询订单数量
//查询交易金额



server.listen(3000, () => {
  console.log('服务器成功!!!');
});

